﻿-- =============================================
-- Script Template
-- =============================================
/*
JobType -
совместитель - 0 ; основное место работы - 1;
*/
SELECT
	  Worker._Period AS DT
	, ChangeType._EnumOrder AS Type
	, CASE WHEN ChangeType._EnumOrder = 0 THEN 'прием на работу' ELSE (CASE WHEN ChangeType._EnumOrder = 1 THEN 'внутреннее перемещение' ELSE 'увольнение' END) END AS TypeName
	, ChangeType._IDRRef, WorkerOffice._Code AS TabNum
	, WorkerOffice._IDRRef AS ID
	, FIOActual.LastName
	, FIOActual.FirstName
	, FIOActual.Otchestvo
	, Professional._Description AS Profesional
	, Departments._Description AS Department
	, WorkSchedule._Description AS Uslovie
	, CASE WHEN Worker._Fld5085 = 1 THEN 1 ELSE 0 END AS JobType
	, CASE WHEN Worker._Fld5085 = 1 THEN 'основное место работы' ELSE 'совместитель' END AS JobTypeName
FROM DB01.Zar_kor_81.dbo._InfoRg5080 AS Worker 
	LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Reference84 AS WorkerOffice 
	LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Reference101 AS FizichjeskieLica 
	LEFT OUTER JOIN
		(SELECT LastName, FirstName, Otchestvo, Period, FizLico 
		FROM dbo.vw_1C_FizFace) AS FIOActual 
			ON FizichjeskieLica._IDRRef = FIOActual.FizLico 
			ON WorkerOffice._Fld993RRef = FizichjeskieLica._IDRRef 
		LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Reference28 AS WorkSchedule 
			ON WorkerOffice._Fld1001RRef = WorkSchedule._IDRRef 
			ON Worker._Fld5081RRef = WorkerOffice._IDRRef 
		LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Enum285 AS ChangeType 
			ON Worker._Fld5096RRef = ChangeType._IDRRef 
	LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Reference38 AS Professional 
		ON Worker._Fld5088RRef = Professional._IDRRef 
	LEFT OUTER JOIN DB01.Zar_kor_81.dbo._Reference67 AS Departments 
		ON Worker._Fld5087RRef = Departments._IDRRef
